﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.AnalysisServices.AdomdClient;

namespace App1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        
        private void button1_Click(object sender, EventArgs e)
        {
          // Open Connection and issue Discover_Commands
          AdomdConnection ASConn = new AdomdConnection("Data Source=" + ServerName.Text 
            + ";Catalog=;Integrated Security=SSPI;");
          ASConn.Open();
          AdomdCommand Cmd = ASConn.CreateCommand();
          Cmd.CommandText = "select * from $system.discover_commands";
          AdomdDataReader R = Cmd.ExecuteReader();

          // Obtain the column names from schema
          DataTable tblSchema = new DataTable("Schema");
          tblSchema = R.GetSchemaTable();

          // Create a DataTable for Discover_Commands
          DataTable tblCommand = new DataTable("Command");
          foreach (DataRow schemarow in tblSchema.Rows)
          { //column name is on the 1st column, data type is on the 6th column
            tblCommand.Columns.Add(schemarow.ItemArray[0].ToString(), 
              System.Type.GetType(schemarow.ItemArray[5].ToString()));
          }

          // Populate the Command DataTable and close the reader
          while (R.Read())
          {
            object[] ColArray = new object[R.FieldCount];
            for (int i = 0; i < R.FieldCount; i++)
            {
              if (R[i] != null) ColArray[i] = R[i];
            }
            tblCommand.LoadDataRow(ColArray, true);
          }
          R.Close();

          // Reuse the Reader for Discover_Sessions
          Cmd.CommandText = "select * from $system.discover_sessions";
          R = Cmd.ExecuteReader();

          // Create a DataTable for Discover_Sessions
          // Obtain the column names from schema (tblSchema is defined above)
          DataTable tblSession = new DataTable("Session");
          tblSchema = R.GetSchemaTable();
          foreach (DataRow schemarow in tblSchema.Rows)
          { //column name is on the 1st column, data type is on the 6th column
            tblSession.Columns.Add(schemarow.ItemArray[0].ToString(), 
              System.Type.GetType(schemarow.ItemArray[5].ToString()));
          }

          // Populate the Session DataTable and close the reader
          while (R.Read())
          {
            object[] ColArray = new object[R.FieldCount];
            for (int i = 0; i < R.FieldCount; i++)
            {
              if (R[i] != null) ColArray[i] = R[i];
            }
            tblSession.LoadDataRow(ColArray, true);
          }
          R.Close();

          // Execute Discover_Connections (reuse reader)
          Cmd.CommandText = "select * from $system.discover_connections";
          R = Cmd.ExecuteReader();

          // Get the column names from schema (tblSchema is defined above)
          DataTable tblConnection = new DataTable("Connection");
          tblSchema = R.GetSchemaTable();
          foreach (DataRow schemarow in tblSchema.Rows)
          { //column name is on the 1st column, data type is on the 6th column
            tblConnection.Columns.Add(schemarow.ItemArray[0].ToString(), 
              System.Type.GetType(schemarow.ItemArray[5].ToString())); 
          }

          // Populate the Connection DataTable
          while (R.Read())
          {
            object[] ColArray = new object[R.FieldCount];
            for (int i = 0; i < R.FieldCount; i++)
            {
              if (R[i] != null) ColArray[i] = R[i];
            }
            tblConnection.LoadDataRow(ColArray, true);
          }

          // Close the reader and the connection
          R.Close();
          ASConn.Close(true);
          ASConn.Dispose();

          // Add both tables to the dataset 
          DataSet DS = new DataSet();
          DS.Tables.Add(tblCommand);
          DS.Tables.Add(tblSession);
          DS.Tables.Add(tblConnection);

          // Create a relationship between Session and Command on Session SPID
          DataColumn ParentColumn = DS.Tables["Session"].Columns["SESSION_SPID"];
          DataColumn ChildColumn = DS.Tables["Command"].Columns["SESSION_SPID"];
          DataRelation Rel = new DataRelation("Session_Command", ParentColumn, ChildColumn);
          DS.Tables["Command"].ParentRelations.Add(Rel);

          // Create a relationship betweeen Connection and Session on Coonection ID
          ParentColumn = DS.Tables["Connection"].Columns["CONNECTION_ID"];
          ChildColumn = DS.Tables["Session"].Columns["SESSION_CONNECTION_ID"];
          DataRelation Rel2 = new DataRelation("Connection_Session", ParentColumn, ChildColumn);
          DS.Tables["Session"].ParentRelations.Add(Rel2);

          // Join the 3 tables (http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx)
          DataTable tblJoin = new DataTable("Join");
          for (int i = 0; i < tblConnection.Columns.Count; i++)
          { 
            tblJoin.Columns.Add(tblConnection.Columns[i].ColumnName, tblConnection.Columns[i].DataType); 
          }

          for (int i = 0; i < tblSession.Columns.Count; i++)
          {
            if (!tblJoin.Columns.Contains(tblSession.Columns[i].ColumnName))
              tblJoin.Columns.Add(tblSession.Columns[i].ColumnName, tblSession.Columns[i].DataType);
            else
              tblJoin.Columns.Add(tblSession.Columns[i].ColumnName + "2", tblSession.Columns[i].DataType);
          }

          for (int i = 0; i < tblCommand.Columns.Count; i++)
          {
            if (!tblJoin.Columns.Contains(tblCommand.Columns[i].ColumnName))
              tblJoin.Columns.Add(tblCommand.Columns[i].ColumnName, tblCommand.Columns[i].DataType);
            else
              tblJoin.Columns.Add(tblCommand.Columns[i].ColumnName + "2", tblCommand.Columns[i].DataType);
          }

          tblJoin.BeginLoadData();
          foreach (DataRow ConnectionRow in tblConnection.Rows)
          {
            DataRow[] SessionRows = ConnectionRow.GetChildRows(Rel2);
            if (SessionRows != null && SessionRows.Length > 0)
            {
              object[] ConnectionArray = ConnectionRow.ItemArray;
              foreach (DataRow SessionRow in SessionRows)
              {
                DataRow[] CommandRows = SessionRow.GetChildRows(Rel);
                if (CommandRows != null && CommandRows.Length > 0)
                {
                  object[] SessionArray = SessionRow.ItemArray;
                  foreach (DataRow CommandRow in CommandRows)
                  {
                    object[] CommandArray = CommandRow.ItemArray;
                    object[] JoinArray = new object[ConnectionArray.Length 
                      + SessionArray.Length + CommandArray.Length];
                    Array.Copy(ConnectionArray, 0, JoinArray, 0, ConnectionArray.Length);
                    Array.Copy(SessionArray, 0, JoinArray, ConnectionArray.Length, SessionArray.Length);
                    Array.Copy(CommandArray, 0, JoinArray, ConnectionArray.Length +
                      SessionArray.Length, CommandArray.Length);
                    tblJoin.LoadDataRow(JoinArray, true);
                  }
                }
              }
            }
          }

          // Bind to DataGridView (AutoSizeColumnsMode can be set)
          dataGridView1.DataSource = tblJoin;
        }
    }
}
